# 海运 不需要找pdf文件
import json
import polars as pl
import xlwings as xw
import os
import win32com.client as win32
import util
from util.common import create_folder_if_not_exists


def create_email_template(box_html, bf_html, mailAttachments, excel_file_path, flag, subject):
    outlook = win32.Dispatch('Outlook.Application')
    # 创建一封邮件
    mail = outlook.CreateItem(0)

    # 邮件主题
    mail.Subject = subject
    # if recipients_to==None:
    #     recipients_to = ''

    # 邮件收件人
    mail.To = util.recipient_email if util.recipient_email else ''
    head = """
            <head>
            <style>
            body {
                    font-size: 14px;     /* 设置字体大小为5号（14px） */
                    font-family: 'SimSun', sans-serif;  /* 设置字体为宋体 */
                }
                table {
                        border-collapse: collapse;
                    }
                th, td {
                    text-align: center;  /* 将文本居中 */
                    padding: 8px;        /* 添加一些内边距以改善可读性 */

                }
                th {
                    background: #86A8E7;
                }
                td {
                    line-height: 7px;    /* 设置行高为10像素 */
                    height: 7px;         /* 设置单元格高度为10像素 */
                }
                .last-row {
                    font-weight: bold;
                    color: #0000DE;
                }
            </style>
            </head>
            """
    # 获取当前邮件的签名（如果有）
    mail.Display()  # 显示邮件窗口，Outlook会自动加载签名
    signature = mail.HTMLBody  # 获取包含签名的当前HTMLBody
    # 邮件正文（文本）
    if bf_html != '':
        custom_body = f"""
            <html>
            {head}
            <body>
            <br />
            {box_html}
             <h3>附电池清单：</h3>
            {bf_html}
            </body>
            </html>
            """
    else:
        custom_body = f"""
                    <html>
                    {head}
                    <body>
                    <br />
                    {box_html}
                    </body>
                    </html>
                    """
    # 将自定义内容添加到签名前
    mail.HTMLBody = custom_body + signature
    mail.Attachments.Add(util.png_path)  # 添加图片
    MAX_ATTACHMENT_SIZE = util.MaximumAttachmentSize * 1024 * 1024  # 10MB
    sumsize = 0
    if flag == 1:  # 空运
        for att in mailAttachments:
            # 累加附件大小
            sumsize += os.path.getsize(att)
        sumsize += os.path.getsize(excel_file_path)
        if sumsize < MAX_ATTACHMENT_SIZE:
            # 添加附件（确保路径正确）
            for att in mailAttachments:
                mail.Attachments.Add(str(att))
            mail.Attachments.Add(excel_file_path)
        else:
            mail.Subject = f"文件大小超过{util.MaximumAttachmentSize}MB，无法添加附件"
            print(f"文件大小超过{util.MaximumAttachmentSize}MB，无法添加附件")
        # 显示邮件前预览
        mail.Display()
    else:  # 海运
        sumsize = os.path.getsize(excel_file_path)
        print(f"附件总大小：{sumsize / 1024 / 1024}MB")
        if sumsize < MAX_ATTACHMENT_SIZE:
            # 添加附件（确保路径正确）
            mail.Attachments.Add(excel_file_path)
        else:
            mail.Subject = f"文件大小超过{util.MaximumAttachmentSize}MB，无法添加附件"
        # 显示邮件前预览
        mail.Display()


def create_oneEmail(item_df, excel_file_path, box_df, subject):
    try:
        tr = ''
        total_rows = box_df.shape[0]
        for i, r in enumerate(box_df.iter_rows(), start=1):
            if i < total_rows:
                tr += f"""
                        <tr>
                        <td>{r[0]}</td>
                        <td>{r[1]}</td>
                        <td>{r[2]}</td>
                        <td>{r[3]}</td>
                        <td>{r[4]}</td>
                        <td>{r[5]}</td>
                        <td>{r[6]}</td>
                        <td>{r[7]}</td>
                    </tr>
                        """
            else:
                tr += f"""
                        <tr class="last-row">
                        <td>{r[0]}</td>
                        <td>{r[1]}</td>
                        <td>{r[2]}</td>
                        <td>{r[3]}</td>
                        <td>{r[4]}</td>
                        <td>{r[5]}</td>
                        <td>{r[6]}</td>
                        <td>{r[7]}</td>
                    </tr>
                        """
        # html模板
        box_html = f"""
                        <table  border=1>
                            <thead>
                                <tr>
                                    <th>箱单号</th>
                                    <th>合同号</th>
                                    <th>贸易术语</th>
                                    <th>危险品标识</th>
                                    <th>计划件数</th>
                                    <th>计划总毛重（KG）</th>
                                    <th>计划总体积（CBM）</th>
                                    <th>预计提货时间</th>
                                </tr>
                            </thead>
                            <tbody>
                               {tr}
                            </tbody>
                        </table>
                        """
        df_html = ''
        if item_df.height > 0:
            tr = ''
            for r in item_df.iter_rows():
                tr += f"""
                    <tr>
                    <td>{r[0]}</td>
                    <td>{r[1]}</td>
                    <td>{r[2]}</td>
                    <td>{r[3]}</td>
                </tr>
                    """
            # html模板
            df_html = f"""
                    <table  border=1>
                        <thead>
                            <tr>
                                <th>箱单号</th>
                                <th>合同号</th>
                                <th>电池货物明细描述</th>
                                <th>子项电池编码信息</th>
                            </tr>
                        </thead>
                        <tbody>
                           {tr}
                        </tbody>
                    </table>
                    """
        create_email_template(box_html, df_html, [], excel_file_path, 2, subject)

    except Exception as e:
        return str(e)


def create_tem(cons, template_df, max_date, d19):
    # 启动 Excel 应用
    try:
        app = xw.App(visible=False)
        template_wb = app.books.open(util.BOOKING_FORM_template_path)  # 打开模板文件
        template_sheet = template_wb.sheets['模板收敛-SEA']
        # 创建一个新的工作薄
        new_wb = xw.Book()
        template_sheet.api.Copy(Before=new_wb.sheets[0].api)
        new_wb.sheets[1].delete()  # 删除默认的工作表
        sht = new_wb.sheets[0]
        for r in template_df.iter_rows():
            # 向单元格写入数据
            sht.range('A2').value = f"{r[0]}\r\n{r[1]}"  # Shipper+Shipper Address
            sht.range('A5').value = r[2]  # 海关申报收货人信息
            sht.range('A16').value = r[3]  # 最终目的地
            sht.range('D16').value = r[4]  # 目的地ID
            sht.range('D19').value = d19
            sht.range('F16').value = r[5]  # 贸易术语
            sht.range('A19').value = r[6]  # 箱单号
            sht.range('C19').value = r[8]  # 计划件数
            sht.range('G19').value = r[9]  # 计划总毛重（KG）
            sht.range('I19').value = r[7]  # 计划总体积（CBM）
            sht.range('I28').value = max_date  # CPD+1天(取最大CPD的日期+1天)
            sht.range('J17').value = r[11]  # 如果贸易术语为FCA，就赋值√，否则就为空
            sht.range('H11').value = r[10]  # 分2种容量的柜子，20GP和40HQ，计算方式=计划总体积（CBM）汇总，样列：20GP[1]; 40HQ[2]  12
            # 保存新的工作薄
            excel_name = ""
            for co in cons:
                excel_name = excel_name + co + " & "
            excel_name = excel_name[:-3]
            create_folder_if_not_exists(util.excel_path)
            excel_file_path = os.path.join(util.excel_path, f"BOOKING FORM-{excel_name}.xlsx")
            new_wb.save(excel_file_path)
            return excel_file_path, excel_name
    except Exception as e:
        raise f"错误：保存新的工作薄失败：{e}"
    finally:
        new_wb.close()
        template_wb.close()
        app.quit()


def fun_sea(datapath, jsonstring):
    df = pl.DataFrame()
    sea_df = util.config_df['sea']
    box = sea_df.filter(pl.col("原字段名") == "箱单号")["现字段名"].item(0)
    stt = sea_df.filter(pl.col("原字段名") == "贸易术语")["现字段名"].item(0)
    dangerous = sea_df.filter(pl.col("原字段名") == "危险品标识")["现字段名"].item(0)
    con = sea_df.filter(pl.col("原字段名") == "合同号")["现字段名"].item(0)
    cbm = sea_df.filter(pl.col("原字段名") == "计划总体积(CBM)")["现字段名"].item(0)
    item = sea_df.filter(pl.col("原字段名") == "危险品货物明细描述")["现字段名"].item(0)
    sub_item = sea_df.filter(pl.col("原字段名") == "子项危险品信息")["现字段名"].item(0)
    qty = sea_df.filter(pl.col("原字段名") == "计划件数")["现字段名"].item(0)
    gw = sea_df.filter(pl.col("原字段名") == "计划总毛重(KG)")["现字段名"].item(0)
    cpd = sea_df.filter(pl.col("原字段名") == "CPD")["现字段名"].item(0)
    dest_id = sea_df.filter(pl.col("原字段名") == "目的地ID")["现字段名"].item(0)
    dest = sea_df.filter(pl.col("原字段名") == "最终目的地")["现字段名"].item(0)
    shipper = sea_df.filter(pl.col("原字段名") == "Shipper")["现字段名"].item(0)
    shipper_address = sea_df.filter(pl.col("原字段名") == "Shipper Address")["现字段名"].item(0)
    customs_info = sea_df.filter(pl.col("原字段名") == "海关申报收货人信息")["现字段名"].item(0)
    if jsonstring == '无':
        df = (pl.read_excel(datapath, sheet_name=sea_df["表名"].head(1).item(),
                            read_options={
                                'use_columns': sea_df["现字段名"].to_list(),
                            },
                            # schema_overrides={"CPD": pl.Date},  # 覆盖默认的列类型
                            engine="calamine")
              .rename(
            {box: 'box', stt: 'stt', dangerous: 'dangerous', con: 'con', cbm: 'cbm',
             item: 'item', sub_item: 'sub_item', qty: 'qty', gw: 'gw',
             cpd: 'cpd', dest_id: 'dest_id', dest: 'dest', shipper: 'shipper',
             shipper_address: 'shipper_address', customs_info: 'customs_info'})  # 重命名列名
              .with_columns(pl.col('cpd').cast(pl.Date))
              .with_columns((pl.col("cpd") + pl.duration(days=1)).alias("cpd_1"))
              .fill_null('')

              )
    else:
        json_object = json.loads(jsonstring)
        df = (
            pl.DataFrame(json_object)
            .rename(
                {box: 'box', stt: 'stt', dangerous: 'dangerous', con: 'con', cbm: 'cbm',
                 item: 'item', sub_item: 'sub_item', qty: 'qty', gw: 'gw',
                 cpd: 'cpd', dest_id: 'dest_id', dest: 'dest', shipper: 'shipper',
                 shipper_address: 'shipper_address', customs_info: 'customs_info'})  # 重命名列名
            .with_columns(pl.col('cpd').cast(pl.Date))
            .with_columns((pl.col("cpd") + pl.duration(days=1)).alias("cpd_1"))
            .fill_null('')
        )
    stt = df['stt'][0]
    dest_id = df['dest_id'][0]
    dest = df['dest'][0]
    dg = pl.Series(df['dangerous'] == 'DG').any()
    d19 = 'Telecommunication equipment 通讯设备 HS CODE:851762'
    if dg:
        d19 = 'Telecommunication equipment 通讯设备 HS CODE:851762\r\nlithium ion batteries 锂电 HS CODE:850760'
    max_date = df['cpd_1'].max()
    cons = df['con'].unique().to_list()
    box_df = df.select(
        pl.col('box', 'con', 'stt', 'dangerous', 'qty', 'gw', 'cbm', 'cpd_1')
    )
    summary_row = pl.DataFrame({
        "box": [""],
        "con": [""],
        "stt": [""],
        "dangerous": ["总计："],
        "qty": [box_df["qty"].sum().__round__(2)],
        "gw": [box_df["gw"].sum().__round__(2)],
        "cbm": [box_df["cbm"].sum().__round__(2)],
        "cpd_1": ['']

    })
    # 将 cpd 列转换为字符串类型
    box_df = box_df.with_columns(pl.col("cpd_1").cast(pl.Utf8))
    summary_row = summary_row.with_columns(pl.col("cpd_1").cast(pl.Utf8))
    box_df = pl.concat(
        [box_df, summary_row],
        how='vertical'
    )

    # 提取item
    item_df = (
        df.select(pl.col('box', 'con', 'item', 'sub_item'))
        .filter(pl.col('item') != '')
        .with_columns(
            pl.col('item').str.replace_all(r',.*?/', '/').alias('item'),
            pl.col('sub_item').str.replace_all(r"90.*?;", '').alias('sub_item')
        )
        .with_columns(
            pl.col('item').str.split('/').alias('item'),
            pl.col('sub_item').str.split('/').alias('sub_item')
        )
        .explode(['item', 'sub_item'])
        .filter(pl.col('item') != '')
    )

    template_df = (
        df.group_by(['shipper', 'shipper_address', 'customs_info', 'dest', 'dest_id', 'stt'])
        .agg(
            pl.col('box').str.concat('\r\n').alias('box'),
            pl.col('cbm').sum().alias('cbm'),
            pl.col('qty').sum().alias('qty'),
            pl.col('gw').sum().alias('gw'),
        )
        .with_columns(
            pl.when(pl.col("cbm") <= 20)
            .then(pl.lit("20GP[1]; 40HQ[0]"))
            .when((pl.col("cbm") > 20) & (pl.col("cbm") <= 40))
            .then(pl.lit("20GP[0]; 40HQ[1]"))
            .otherwise(
                pl.when(pl.col("cbm") % 40 > 20)
                .then(
                    pl.concat_str([pl.lit("20GP[0]; 40HQ["), ((pl.col("cbm") // 40) + 1).cast(pl.Int32), pl.lit("]")])
                )
                .otherwise(
                    pl.concat_str([pl.lit("20GP[1]; 40HQ["), (pl.col("cbm") // 40).cast(pl.Int32), pl.lit("]")])
                )
            ).alias("container_type")
        )
        .with_columns(
            pl.when(pl.col('stt') == 'FCA')
            .then(pl.lit('√'))
            .otherwise(pl.lit(''))
            .alias('fca')
        )

    )
    excel_file_path, con_name = create_tem(cons, template_df, max_date, d19)
    subject = f"【请释放SO】{stt} {dest_id} TO {dest} -- {con_name}"
    create_oneEmail(item_df, excel_file_path, box_df, subject)
